<?php
defined( 'SYSPATH' ) or die( 'No direct access allowed.' );
/*
 * 采购付款报表
 * 
 */
class Controller_finance extends Controller_Admin
{
	public function before(){
	
		try {
			ACL::Required('finance form');
		} catch ( HTTP_Exception_403 $e ) {
			Message::error('sorry，你没有权限进这个目录');
			$this->request->redirect ( URL::base (true) );
		}
		
		parent::before();
	}
	
	/*
	 * 采购付款初始页面
	 */
	public function Action_index()
	{
		$this->title = __("采购单付款报表");
		$view = View::factory('form/index');
		$this->response->body($view);
	}
	
	public function Action_select()
	{
		$sql = " SELECT purchaseorders.id AS poid , ";
		$sql .= " purchaseorders.purchase_id , ";
		$sql .= " purchaseorders.purchaseorder_date , ";
		$sql .= " purchaseorders.supplier_id ,";
		$sql .= " purchaseorders.pay_way , ";
		$sql .= " purchaseorders.transportation_expense , ";
		$sql .= " purchaseorders.status";
		
		if(isset($_GET))
		{
			$sql  .= $this->get_sql($_GET);
		}
		
		$select = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();
		
		$re = $this->get_convert_data($select, $_GET);
		
		$view = View::factory('form/table');
		$view->set("pay_lists",$re);
		$view->render();
		
		echo  $view;
		exit;
	}
	
	public function Action_select_sum()
	{
		$re = array();
		$sql = "";
		$sql .= " SELECT purchaseorders.id AS count_ids ";
		
		if(isset($_GET))
		{
			$sql  .= $this->get_sql($_GET);
		}
		
		$select = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();
		
		$re['count_ids'] = count($select);
		
		
		echo json_encode($re);exit;
	}
	
	public function Action_load()
	{
		require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
			
			$objPHPExcel      = new PHPExcel();
			$objPHPExcel->getProperties()->setCreator("YKS")
			->setLastModifiedBy("YKS")
			->setTitle("Office 2003 XLSX Test Document")
			->setSubject("Office 2003 XLSX Test Document")
			->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
			->setKeywords("office 2003 openxml php")
			->setCategory("Test result file");
			$objActSheet =$objPHPExcel->setActiveSheetIndex(0);
			
			$objActSheet->setCellValue('A1', '序号');
			$objActSheet->setCellValue('B1', '采购员');
			$objActSheet->setCellValue('C1', '采购日期');
			$objActSheet->setCellValue('D1', '采购单号');
			$objActSheet->setCellValue('E1', '供应商');
			$objActSheet->setCellValue('F1', '供应商编号');
			$objActSheet->setCellValue('G1', '付款方式');
			$objActSheet->setCellValue('H1', '采购物品总额');
			$objActSheet->setCellValue('I1', '运费');
			$objActSheet->setCellValue('J1', '该段时间付款总额');
			$objActSheet->setCellValue('K1', '采购单付款总额');
 			$objActSheet->setCellValue('L1', '采购单状态');
			$objActSheet->setCellValue('M1', '第一次到货时间');
			
		
		$sql = " SELECT purchaseorders.id AS poid , ";
		$sql .= " purchaseorders.purchase_id , ";
		$sql .= " purchaseorders.purchaseorder_date , ";
		$sql .= " purchaseorders.supplier_id ,";
		$sql .= " purchaseorders.pay_way , ";
		$sql .= " purchaseorders.transportation_expense , ";
		$sql .= " purchaseorders.status";
		
		if(isset($_GET))
		{
			$sql  .= $this->get_sql($_GET);
		}
		
		$select = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();
		
		$re = $this->get_convert_data($select, $_GET);
		
		if($re)
		{
			
			
			foreach ($re as $key => $value)
			{
				$objActSheet->setCellValue('A'.($key+2), $value['num']);
				$objActSheet->setCellValue('B'.($key+2), $value['purchaser_name']);
				$objActSheet->setCellValue('C'.($key+2), $value['purchaseorder_date']);
				$objActSheet->setCellValue('D'.($key+2), $value['poid']);
				$objActSheet->setCellValue('E'.($key+2), $value['supplier_name']);
				$objActSheet->setCellValue('F'.($key+2), $value['supplier_id']);
				$objActSheet->setCellValue('G'.($key+2), $value['pay_way_string']);
				$objActSheet->setCellValue('H'.($key+2), $value['money_total']);
				$objActSheet->setCellValue('I'.($key+2), $value['transportation_expense']);
				$objActSheet->setCellValue('J'.($key+2), $value['pay']);
				$objActSheet->setCellValue('K'.($key+2), $value['pay_sum']);
 				$objActSheet->setCellValue('L'.($key+2), $value['status_string']);
				$objActSheet->setCellValue('M'.($key+2), $value['first_arrive_time']);

			}
		}	
			$objPHPExcel->getActiveSheet()->setTitle('采购付款报表');
			$objPHPExcel->setActiveSheetIndex(0);
			$day=date("m-d");
			$filename=$day.'采购付款报表.xls';
            ob_end_clean();
			header("Content-Type: application/vnd.ms-excel; charset=utf-8");
			header('Content-Disposition: attachment;filename='.$filename);
			header('Cache-Control: max-age=0');
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
			$objWriter->save('php://output');
			exit;
	}
	
	protected function get_sql(array $select)
	{
		$sql = '';
		
		$sql .= " FROM purchaseorder_pays  JOIN purchaseorders ON purchaseorder_pays.purchaseorder_id = purchaseorders.id ";
		$sql .= " WHERE 1 ";
		
		if(isset($select['purchaseorder_date_from']) and trim($select['purchaseorder_date_from']))
		{
			$sql .= " AND purchaseorders.purchaseorder_date >= ' ". trim($select['purchaseorder_date_from'])." 00:00:00 ' ";
		}
		
		if(isset($select['purchaseorder_date_to']) and trim($select['purchaseorder_date_to']))
		{
			$sql .= " AND purchaseorders.purchaseorder_date <= ' ".trim($select['purchaseorder_date_to'])." 23:59:59' ";
		}
		
		if(isset($select['supplier_name']) and trim($select['supplier_name']))
		{
			$supplier_ids = '-1';
			//START----------------------------------------cwy-------------------------------------------------------------
            $name = trim($select['supplier_name']);
            $supp_id = Newerpclientapi::getSuppliers(array('id'),array('name' => "like '%$name%'"));
//			$supp_sql = " SELECT id FROM erp_provider WHERE vProviderName
//			LIKE '%".trim($select['supplier_name'])."%'";
//			$supp_id = DB::query(Database::SELECT, $supp_sql)->execute('readonlyerp')->as_array();
			//END----------------------------------------cwy---------------------------------------------------------------
			if($supp_id)
			{
				foreach ($supp_id as $spid)
				{
					$supplier_ids .= ",".$spid['id'];
				}
			}
			
			$sql .= " AND purchaseorders.supplier_id IN ( ".$supplier_ids." ) ";
		}
		
		if(isset($select['purchaser_name']) and trim($select['purchaser_name']))
		{
			$purchaserid = Erpuser::lookup_by_nick($select['purchaser_name']);
			$purchaserid = $purchaserid?$purchaserid:-1;
			
			$sql .= " AND purchaseorders.purchase_id =  ".$purchaserid;
		}
		
		if(isset($select['pay_date_from']) and trim($select['pay_date_from']))
		{
			$sql .= " AND purchaseorder_pays.pay_date >= ' ".trim($select['pay_date_from'])." 00:00:00 '";
		}
		
		if(isset($select['pay_date_to']) and trim($select['pay_date_to']))
		{
			$sql .= " AND purchaseorder_pays.pay_date <= ' ".trim($select['pay_date_to'])." 23:59:59 ' ";
		}
		
		$sql .= " GROUP BY purchaseorders.id";
		
		if(isset($select['limit']) and trim($select['limit']))
		{
			$sql .= " LIMIT  ".trim($select['limit']);
		}
		
		if(isset($select['offset']) and trim($select['offset']))
		{
			$sql .= " OFFSET ".trim($select['offset']);
		}
		
		
		
		
		return $sql;
	}
	
	protected function get_convert_data($data,$select)
	{
		if(empty($data))
		{
			return NULL;
		}
		
		foreach ($data as $key=>$value)
		{
			//序号
			$num = isset($key)?$key:0;
			
			if(isset($select['offset']) and trim($select['offset']))
			{
				$num += $select['offset'];
			}
			$data[$key]['num'] = isset($num)?$num:0;
			$data[$key]['num'] += 1;
			//采购员名称
			$data[$key]['purchaser_name'] = Erpuser::nick_by_id($value['purchase_id']);
			
			//供应商名称
			$data[$key]['supplier_name'] = Basic::getnameByid($value['supplier_id']);
			
			//付款方式
			$pay_way_id = isset($value['pay_way'])?$value['pay_way']:-1;
			$pay_way_sql = " SELECT type ";
			$pay_way_sql .= " FROM erp_pay_type ";
			$pay_way_sql .= " WHERE id = ".$pay_way_id;			
			$pay_way = DB::query(Database::SELECT, $pay_way_sql)->execute('readonlyerp')->current();
			
			$data[$key]['pay_way_string'] = isset($pay_way['type'])?$pay_way['type']:"未知付款方式";
			
			//采购单号
			$poid = isset($value['poid'])?$value['poid']:-1;
			
			//货款
			$money_total_sql = " SELECT SUM(purchaseorder_details.money) AS money_total ";
			$money_total_sql .= " FROM purchaseorder_details ";
			$money_total_sql .= " WHERE status >10 AND  status < 100";
			$money_total_sql .= " AND purchaseorder_id = ".$poid;
			
 			$money_total = DB::query(Database::SELECT, $money_total_sql)->execute('newerp')->current();
			$data[$key]['money_total'] = isset($money_total['money_total'])?$money_total['money_total']:0;
			$data[$key]['money_total'] = round($data[$key]['money_total'],2);
			
			//运费
			$data[$key]['transportation_expense'] = isset($value['transportation_expense'])?$value['transportation_expense']:0;
			$data[$key]['transportation_expense'] = round($data[$key]['transportation_expense'],2);
			
			//采购单付款总额
			$pay_sum_sql = " SELECT SUM(amount_paid) AS pay_sum ";
			$pay_sum_sql .= " FROM purchaseorder_pays ";
			$pay_sum_sql .= " WHERE purchaseorder_id = ".$poid;
			$pay_sum = DB::query(Database::SELECT, $pay_sum_sql)->execute('newerp')->current();
			
			$data[$key]['pay_sum'] = isset($pay_sum['pay_sum'])?$pay_sum['pay_sum']:0;
			$data[$key]['pay_sum'] = round($data[$key]['pay_sum'],2);
			
			//该段时间付款总额
			$pay_sql = $pay_sum_sql;
			if(isset($select['pay_date_from']) and trim($select['pay_date_from']))
			{
				$pay_sql .= " AND pay_date >= ' ".trim($select['pay_date_from'])." ' ";
			}
			if(isset($select['pay_date_to']) and trim($select['pay_date_to']))
			{
				$pay_sql .= " AND pay_date <= ' ".trim($select['pay_date_to'])." 23:59:59 ' ";
			}
			$pay = DB::query(Database::SELECT, $pay_sql)->execute('newerp')->current();
			
			$data[$key]['pay'] = isset($pay['pay_sum'])?$pay['pay_sum']:0;
			$data[$key]['pay'] = round($data[$key]['pay'] ,2);
			
			//采购单状态
			$po_status_id= isset($value['status'])?$value['status']:0;
			$po_status_sql = " SELECT number, value ";
			$po_status_sql .= " FROM statusdics ";
			$po_status_sql .= " WHERE 1 ";
			$po_status_sql .= " AND  `table` =  'purchaseorders' ";
			$po_status_sql .= " AND  `colum_name` =  'status' ";
			$po_status_sql .= " AND number= ".$po_status_id;
			$po_status = DB::query(Database::SELECT, $po_status_sql)->execute('newerp')->current();
			
			$data[$key]['status_string'] = isset($po_status['value'])?$po_status['value']:"未知状态";
			
			//第一次到货时间
			$first_arrive_time_sql	= " SELECT recieve_time  ";		
			$first_arrive_time_sql .= " FROM recieve_invoices ";
			$first_arrive_time_sql .= " WHERE purchaseorder_id = ".$poid;
			$first_arrive_time = DB::query(Database::SELECT, $first_arrive_time_sql)->execute('newerp')->current();
			
			$data[$key]['first_arrive_time'] = isset($first_arrive_time['recieve_time'])?$first_arrive_time['recieve_time']:0;
			
		}
			
		return $data;
	}
}
